-- oracle to desc
SELECT
T1.COLUMN_NAME Field_Name,
T1.DATA_TYPE || '(' || T1.DATA_LENGTH || ')' Type,
T1.NULLABLE
FROM USER_TAB_COLS T1, USER_COL_COMMENTS T2
WHERE T1.TABLE_NAME = T2.TABLE_NAME
AND T1.COLUMN_NAME = T2.COLUMN_NAME
AND T1.TABLE_NAME ='BD_CITY' ;
-- oracle to mysql table
SELECT
T1.COLUMN_NAME Field_Name,
DECODE(T1.DATA_TYPE,'VARCHAR2','varchar(' || T1.DATA_LENGTH || ')',
'DATE','datetime',
'NUMBER','bigint(' || T1.DATA_LENGTH || ')',
T1.DATA_TYPE || '(' || T1.DATA_LENGTH || ')' )Type,
DECODE(T1.NULLABLE,'Y','DEFAULT NULL','NOT NULL') NULLABLE,
FROM USER_TAB_COLS T1, USER_COL_COMMENTS T2
WHERE T1.TABLE_NAME = T2.TABLE_NAME
AND T1.COLUMN_NAME = T2.COLUMN_NAME
AND T1.TABLE_NAME ='S_USER'
order by T1.COLUMN_Id;
-- to mysql PRIMARY KEY AUTO_INCREMENT
select 'ALTER TABLE ' || substr(t1.sequence_name,5,100) ||' CHANGE ' || t2.COLUMN_NAME || ' '|| t2.COLUMN_NAME|| ' '||
DECODE(T2.DATA_TYPE,'VARCHAR2','varchar(' || T2.DATA_LENGTH || ')',
'DATE','datetime',
'NUMBER','bigint(' || T2.DATA_LENGTH || ')',
T2.DATA_TYPE || '(' || T2.DATA_LENGTH || ')' )||' NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY ('||t2.COLUMN_NAME||');' pk
from user_sequences t1 inner join USER_TAB_COLS t2 on (t2.TABLE_NAME=substr(t1.sequence_name,5,100) and t2.COLUMN_Id=1)
where t1.sequence_name like 'SEQ_BD_%' or t1.sequence_name like 'SEQ_S_%';
-- oracle --> mysql
select 'CREATE TABLE ' || TABLE_NAME || ' (' || max(r) ||' , PRIMARY KEY ('||max(pk)||')) ENGINE=InnoDB DEFAULT CHARSET=utf8' create_table
from (
select TABLE_NAME, wm_concat(cols) over (partition by TABLE_NAME order by COLUMN_Id) r,pk
from(
SELECT T1.TABLE_NAME,T1.COLUMN_Id,
(T1.COLUMN_NAME || ' ' ||
DECODE(T1.DATA_TYPE, 'VARCHAR2', 'varchar(' || T1.DATA_LENGTH || ')',
'DATE', 'datetime',
'NUMBER', 'bigint(' || T1.DATA_LENGTH || ')',
T1.DATA_TYPE || '(' || T1.DATA_LENGTH || ')') || ' ' ||
DECODE(T1.NULLABLE, 'Y', 'DEFAULT NULL', 'NOT NULL') || ' ' ||
DECODE(T1.COLUMN_Id, 1, DECODE(T3.sequence_name, null, '', 'AUTO_INCREMENT'), '') || ' ' ||
) cols,
DECODE(T1.COLUMN_Id, 1, DECODE(T3.sequence_name, null, '', T1.COLUMN_NAME), '')pk
FROM USER_TAB_COLS T1 inner join USER_COL_COMMENTS T2
on (T1.TABLE_NAME = T2.TABLE_NAME AND T1.COLUMN_NAME = T2.COLUMN_NAME)
left join user_sequences T3 on (T1.TABLE_NAME = substr(T3.sequence_name, 5, 100))
)
)
WHERE
TABLE_NAME = 'S_RESOURCE'
group by TABLE_NAME ;